/// ===========================================================================
/// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
/// KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
/// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
/// PURPOSE.
/// ===========================================================================
/// 
/// Project:        MOSS Faceted Search
/// Author:         Leonid Lyublinski (leonidly@microsoft.com)
/// Company:        Microsoft Services
/// Date:           05/04/2007  Version:        1.0
///
/// ===========================================================================

using System;
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Xml.Serialization;
using System.Data;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.Office.Server;
using Microsoft.Office.Server.Search.Query;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using EntLib = Microsoft.Practices.EnterpriseLibrary;

namespace Microsoft.SharePoint.Portal.ExtendedSearch.Common
{
    /// <summary>
    /// Contains methods to build Sql statement
    /// </summary>
    /// <remarks>Implementation of the faceted search relies on </remarks>
    internal class SqlBuilder
    {
        #region Fields
        private string _scope;
        private string _k;
        private string _hiddenRule;
        private NameValueCollection _properties;
        StringCollection _columns;
        private bool _prefixEnabled;
        private FormData _formData;
        private QueryMethodEnum _queryMethod;
        private bool _isAdvancedSearch;

        #endregion

        #region Constructors
        public SqlBuilder(SearchQuery query, StringCollection columns)
        {
            _scope = query.Scope;
            _k = query.Keywords;
            _properties = query.Properties;
            _columns = columns;
            _formData = query.FormData;
            _queryMethod = query.QueryMethod;
            _isAdvancedSearch = query.IsAdvancedSearch;
        }
        public SqlBuilder(SearchQuery query, StringCollection columns, bool useContains): this (query, columns)
        {
            _prefixEnabled = useContains;
        }
        #endregion

        #region Properties           
        /// <summary>
        /// Constructs final SQL statement from the querystring
        /// </summary>
        public string SqlQuery
        {
            get
            {
                string sql = GetSELECT();
                sql += GetFROM();
                sql += GetWHERE();
                sql += GetORDERBY();
                return sql;
            }
        }
        #endregion

        #region Methods
        /// <summary>
        /// Constructs SELECT SQL statement from the querystring based on defined 
        /// facets through the WP UI.
        /// </summary>
        /// <returns></returns>
        private string GetSELECT()
        {
            string select = " SELECT Path ";
            string columns = GetParsedColumns();
            if (!columns.Equals(string.Empty))
                select = select + ", " + columns;

            return select;
        }

        private string GetFROM()
        {
            return " FROM scope() ";
        }

        private string GetWHERE()
        {
            ArrayList where = new ArrayList();
            if (!_isAdvancedSearch)
            {
                if (!string.IsNullOrEmpty(_k))
                {
                    string searchFor;
                    if (_k.StartsWith("\"") && _k.EndsWith("\""))
                    {
                        searchFor = _k.TrimEnd('"').TrimStart('"');
                    }
                    else
                    {
                        Regex re = new Regex(@"\s+");
                        searchFor = re.Replace(_k, "\" AND \"");
                    }
                    //where.Add(" CONTAINS('\"" + _k + "*\"') "); // doesn't conform core results totals
                    where.Add(" CONTAINS('\"" + searchFor + "\"') ");
                }
                if (!string.IsNullOrEmpty(_scope))
                    where.Add("\"scope\"='" + _scope + "' ");

                for (int i = 0; i < _properties.Count; i++)
                {
                    string propName = _properties.Keys[i];
                    string propValue = _properties[propName].TrimEnd('%');

                    propValue = propValue.Replace(']', '_').Replace('[', '_').Replace('%', '_');    // input from Ascentum
                    //where.Add(" CONTAINS(\"" + propName + "\", '\"" + propValue + "\"') "); // no wild card here
                    // when ManagedProperty has FullTextQueriable false, contains will make Facets dissapear. So will try LIKE
                    if (_prefixEnabled)
                        where.Add(" \"" + propName + "\" LIKE '%" + propValue + "%' "); // wild card added to match core results
                    else
                        where.Add(" \"" + propName + "\" LIKE '" + propValue + "%' "); // wild card added to match core results
                }
            }
            else
            {
                if (!_formData.IsEmpty())
                {
                    string scopes = _formData.GetScopesSql();
                    if (!string.IsNullOrEmpty(scopes))
                        where.Add(string.Format("({0})", scopes));
                    string languages = _formData.GetLanguagesSql();
                    if (!string.IsNullOrEmpty(languages))
                        where.Add(string.Format("({0})", languages));
                    string k = _formData.Keywords.GetKeywordsFreetext();
                    if (!string.IsNullOrEmpty(k))
                        where.Add(k);
                    string props = _formData.GetPropsFreetext();
                    if (!string.IsNullOrEmpty(props))
                        where.Add(string.Format("({0})", props));
                    string resTypes = _formData.ResultType;
                    if (!string.IsNullOrEmpty(resTypes))
                        where.Add(string.Format("({0})", resTypes));
                }
            }
            string result = string.Empty;
            if (where.Count > 0)
                result = " WHERE " + Splitter.Join(where, " AND ");
            return result;
        }

        private string GetORDERBY()
        {
            string orderby = " ORDER BY \"Rank\" DESC ";
            string columns = GetParsedColumns();
            if (!columns.Equals(string.Empty))
                orderby = orderby + ", " + columns;
            return String.Empty; //for now
        }


        /// <summary>
        /// Returns quoted delimited string of columns, used in building SQL
        /// </summary>
        /// <returns></returns>
        private string GetParsedColumns()
        {
            string columnsStr = string.Empty;
            if (_columns.Count > 0)
            {
                ArrayList al = new ArrayList(_columns);
                //columnsStr = String.Join("\", \"", (string[])al.ToArray(typeof(string)));
                columnsStr = Splitter.Join(al, "\", \"");
                columnsStr = "\"" + columnsStr + "\"";
            }
            return columnsStr;
        }
        #endregion
    }
}